package com.wanczy.dao.impl;

import java.sql.Connection;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.wanczy.dao.RoleTableDAO;
import com.wanczy.pojo.RoleTable;

public class RoleTableDAOImpl implements RoleTableDAO {
	public Connection conn;
	public RoleTableDAOImpl(Connection conn) {
		this.conn = conn;
	}
	@Override
	public List<RoleTable> find(String name,int pageSize,int pageCurrent) {
		List<RoleTable> list = null;
		try {
			QueryRunner qRunner = new QueryRunner();// 创建SQL的执行工具
			String sql = "select role_id,role_name,role_describe,role_state  from " + 
					"( select role_id,role_name,role_describe,role_state ,rownum as aa from role_table where role_name like ? ) " + 
					"where aa > ? and aa <= ? ";
			Object params[] = {"%"+name+"%",pageSize*(pageCurrent-1),pageCurrent*pageSize};
			list =  (List<RoleTable>) qRunner.query(conn, sql,new BeanListHandler<>(RoleTable.class),params);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public boolean doUpd(RoleTable role) {
		boolean flag = false;
		try {
			QueryRunner qRunner = new QueryRunner();// 创建SQL的执行工具
			String sql = "update role_table set role_name = ?, role_describe = ?,role_state = ? where role_id = ? ";
			Object params[] = {role.getRole_name(),role.getRole_describe(),role.getRole_state(),role.getRole_id()};
			int x = qRunner.update(conn, sql, params);
			if(x == 1) {
				flag =  true;
			}else {
				flag =  false;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public boolean doIns(RoleTable role) {
		boolean flag = false;
		try {
			QueryRunner qRunner = new QueryRunner();// 创建SQL的执行工具
			String sql = "insert into role_table( role_id,role_name,role_describe,role_state) values (LUO.nextval,?,?,1)";
			Object param[] = {role.getRole_name(),role.getRole_describe()};
			int x = qRunner.update(conn, sql, param);
			if(x == 1) {
				flag =  true;
			}else {
				flag =  false;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} 
		return flag;
	}

	@Override
	public boolean doDel(int roleId) {
		boolean flag = false;
		try {
			QueryRunner qRunner = new QueryRunner();// 创建SQL的执行工具
			String sql = "update role_table set role_state = 0 where role_id = ?";
			Object param[] = {roleId};
			int x = qRunner.update(conn, sql, param);
			if(x == 1) {
				flag =  true;
			}else {
				flag =  false;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} 
		return flag;
	}
	@Override
	public List<RoleTable> findByUserId(int userId) {
		List<RoleTable> list = null;
		try {
			QueryRunner qRunner = new QueryRunner();// 创建SQL的执行工具
			String sql = "select role_id,role_name,role_describe,role_state from role_table " + 
					"where role_id in (select role_id from user_role_table where user_id = ?)";
			Object params[] = {userId};
			list =  (List<RoleTable>) qRunner.query(conn, sql,new BeanListHandler<>(RoleTable.class),params);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	@Override
	public List<RoleTable> findByRoelId(int roleId) {
		List<RoleTable> list = null;
		try {
			QueryRunner qRunner = new QueryRunner();// 创建SQL的执行工具
			String sql = "select role_id,role_name,role_describe,role_state from role_table where role_id = ? ";
			Object params[] = {roleId};
			list =  (List<RoleTable>) qRunner.query(conn, sql,new BeanListHandler<>(RoleTable.class),params);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	@Override
	public int findCount(String name) {
		int x = 0;
		try {
			QueryRunner qRunner = new QueryRunner();// 创建SQL的执行工具
			String sql = "select role_name from role_table where role_name like ? ";
			Object params[] = {"%"+name+"%"};
			List<RoleTable> list =  (List<RoleTable>) qRunner.query(conn, sql,new BeanListHandler<>(RoleTable.class),params);
			x = list.size();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return x;
	}

}
